<?php defined('SYSPATH') OR die('No direct access allowed.');

class Model_Cms_Show extends Model {
	
	public function last_posts($category_id,$limit)
	{
		$p = DB::Select()->from('pages');
		$p->where('category_id','=',$category_id);
		if(Auth::instance()->logged_in('admin') == 0)
			$p->and_where('published','=',1);
		$p->order_by('created_at','desc');
		$p->limit($limit);
		return $p->execute();
	}
	
	/**
	 * pobieranie oceny i liczby glosow do boxow skrotow filmikow
	 * do strony startowej (glownie)
	 * @param  video_id
	 * @return array - ocena i liczba glosow
	 * @todo w kontrolerze show jest taka sama funkcja - rate_words()
	 */
	private function get_rating_start($id)
	{
		$rating_count = DB::select(DB::expr('COUNT(*) AS mycount'))->from('rating')
			->where('id_page','=',$id)
			->execute()
			->get('mycount');
		// sprawdzenie czy sa oceny tego postu
		if($rating_count > 0)
		{
			$pob = DB::select()->from('rating')->where('id_page','=',$id)->as_object()->execute();
			foreach($pob as $pb)
			{
				$rat = (@round($pb->value / $pb->count,1));
				$num = $pb->count;
				$rating = array('rating'=>$rat,'rating_num'=>$num);
			}
		}else{
			$rating = array('rating'=>0,'rating_num'=>0);
		}
		
		return $rating;
	}
	
	// pobiera najnowszy filmik z każdej ligi
	public function get_first_ligues()
	{
		$ligues = DB::Select('id')->from('ligues')
			->execute();
			
		$vids = array();
		$i = 0;
		foreach($ligues as $l)
		{
			$p = DB::Select(array('videos.id','video_id'),array('videos.thumbnail','thumbnail'),array('videos.code','code'),array('videos.text_stripped','text'))->from('kolejka');
			$p->join('videos','left')->on('videos.kolejka_id','=','kolejka.id');
			$p->where('kolejka.ligue_id','=',$l['id']);
			$p->order_by('videos.created_at','desc');
			$v = $p->execute()->current();
			
			$vids[$i]['id'] = $v['video_id'];
			$vids[$i]['thumbnail'] = $v['thumbnail'];
			$vids[$i]['text'] = $v['text'];
			$vids[$i]['liga'] = $this->get_ligue_by_video($v['video_id']);
			$rate = $this->get_rating_start($v['video_id']);
			$vids[$i]['rating'] = $rate['rating_num'];
			$vids[$i]['rating_number'] = $rate['rating'];
			$i++;
		}
		return $vids;
	}
	
	/**
	 * wybiera filmiki oznaczone jako bramka tygodnia
	 * do strony startowej
	 * @param  ilosc filmow - limit
	 * @return array - video
	 */
	public function get_videos_goal($limit)
	{
		$p = DB::Select()->from('videos');
		$p->where('bramka','=',1);
		if(Auth::instance()->logged_in('admin') == 0)
			$p->and_where('published','=',1);
		$p->limit($limit);
		$p->order_by('created_at','desc');
		$videos = $p->execute();
		
		$vids = array();
		$i = 0;
		foreach($videos as $v)
		{
			$vids[$i]['id'] = $v['id'];
			$vids[$i]['thumbnail'] = $v['thumbnail'];
			$vids[$i]['text'] = $v['text_stripped'];
			$vids[$i]['code'] = $v['code'];
			$vids[$i]['name'] = $v['name'];
			$rate = $this->get_rating_start($v['id']);
			$vids[$i]['rating'] = $rate['rating_num'];
			$vids[$i]['rating_number'] = $rate['rating'];
			$i++;
		}
		return $vids;
	}
	
	/**
	 * wybiera filmiki oznaczone jako bramka tygodnia
	 * do strony startowej
	 * @param  ilosc filmow - limit
	 * @return array - video
	 */
	public function video_last_added()
	{
		return DB::Select()->from('videos')
			->where('published','=',1)
			->order_by('created_at','desc')
			->limit(1)
			->execute()->current();
	}
	
/**
	 * ostatnio dodane do prawej kolumny
	 * @return array - video, limit 5
	 */
	public function last_added_right()
	{
		$videos = DB::Select()->from('videos')
			->where('published','=',1)
			->order_by('created_at','desc')
			->limit(5)
			->execute();
			
		$vids = array();
		$i = 0;
		foreach($videos as $v)
		{
			$vids[$i]['id'] = $v['id'];
			$vids[$i]['thumbnail'] = $v['thumbnail'];
			$vids[$i]['text'] = $v['text_stripped'];
			$vids[$i]['code'] = $v['code'];
			$vids[$i]['name'] = $v['name'];
			$rate = $this->get_rating_start($v['id']);
			$vids[$i]['rating'] = $rate['rating_num'];
			$vids[$i]['rating_number'] = $rate['rating'];
			$i++;
		}
		return $vids;
	}
	
	/**
	 * wybiera filmiki oznaczone jako bramka tygodnia
	 * do strony startowej
	 * @param  ilosc filmow - limit
	 * @return array - video
	 */
	public function videos_most_viewed($limit)
	{
		$p = DB::Select(array('videos.id','video_id'),array('videos.thumbnail','thumbnail'),array('videos.text_stripped','text'),array('videos.name','name'),array('videos.code','code'))->from('visits');
		$p->join('videos','left')->on('videos.id','=','visits.video_id');
		if(Auth::instance()->logged_in('admin') == 0)
			$p->and_where('published','=',1);
		$p->limit($limit);
		$p->order_by('visits.number','desc');
		$videos = $p->execute();
		
		$vids = array();
		$i = 0;
		foreach($videos as $v)
		{
			$vids[$i]['video_id'] = $v['video_id'];
			$vids[$i]['thumbnail'] = $v['thumbnail'];
			$vids[$i]['text'] = $v['text'];
			$vids[$i]['code'] = $v['code'];
			$vids[$i]['name'] = $v['name'];
			$rate = $this->get_rating_start($v['video_id']);
			$vids[$i]['rating'] = $rate['rating_num'];
			$vids[$i]['rating_number'] = $rate['rating'];
			$i++;
		}
		return $vids;
	}
	
	/**
	 * wybiera filmiki podobne na podstawie tagów
	 * do prawej kolumny widoku poszczególnych filmików
	 * @param  video_id, ilosc filmow - limit
	 * @return array - video
	 */
	public function videos_podobne($id,$limit)
	{
		// wybieranie tagów wideo wejsciowego
		$input = DB::Select('tags')->from('videos')
			->where('id','=',$id)
			->execute()->current();
		$tags = $input['tags'];
		
		if($tags == '')
			return null;
		
		$t_array = explode(',',$tags);
		$o_array = array();
		foreach($t_array as $ta)
		{
			$o_array[] = trim($ta);
		}
		
		$p = DB::Select()->from('videos');
		if(Auth::instance()->logged_in('admin') == 0)
			$p->and_where('published','=',1);
			
		foreach($o_array as $o)
		{
			$p->or_where('tags','like','%'.$o.'%');
		}
		
		$p->limit($limit);
		$p->order_by('created_at','desc');
		$videos = $p->execute();
				
		$vids = array();
		$i = 0;
		foreach($videos as $v)
		{
			if($v['id'] != $id)
			{
				$vids[$i]['id'] = $v['id'];
				$vids[$i]['thumbnail'] = $v['thumbnail'];
				$vids[$i]['text'] = $v['text_stripped'];
				$vids[$i]['name'] = $v['name'];
				$rate = $this->get_rating_start($v['id']);
				$vids[$i]['rating'] = $rate['rating_num'];
				$vids[$i]['rating_number'] = $rate['rating'];
				$i++;
			}
		}
		return $vids;
	}
	
	// wejście - id video
	// zwraca nazwę ligi
	public function get_ligue_by_video($video_id)
	{
		$p = DB::Select(array('ligues.name','ligue_name'))->from('ligues');
		$p->join('kolejka','left')->on('kolejka.ligue_id','=','ligues.id');
		$p->join('videos','left')->on('videos.kolejka_id','=','kolejka.id');
		$p->where('videos.id','=',$video_id);
		$liga = $p->execute()->current();
		return $liga['ligue_name'];
	}
	
	/**
	 * pobiera liste lig wraz z ostatnia kolejka 
	 * i przypisanymi do niej filmami
	 * @param  id ligi
	 * @return multidim. array - liga - ostatnia kolejka - filmy (5)
	 */
	public function get_ligues()
	{
		$output = array();
		$ligi = DB::Select()->from('ligues')
			->execute();
		$i=0;
		foreach($ligi as $l)
		{
			// wybieranie ostatniej kolejki danej ligi
			$kolejka = DB::Select()->from('kolejka')
				->where('ligue_id','=',$l['id'])
				->order_by('created_at','desc')
				->limit(1)
				->execute()->current();
			// wybieranie filmow do kolejki
			$filmy = DB::Select()->from('videos')
				->where('kolejka_id','=',$kolejka['id'])
				->order_by('created_at','desc')
				->limit(5)
				->execute();
			$output[$i]['videos'] = $filmy;
			$output[$i]['liga_name'] = $l['name'];
			$output[$i]['liga_id'] = $l['id'];
			$output[$i]['kolejka_name'] = $kolejka['name'];
			$output[$i]['kolejka_id'] = $kolejka['id'];
			$i++;
		}
		return $output;
	}
	
	/**
	 * pobiera filmy danej kolejki
	 * @param  id kolejki
	 * @return array - liga - filmy
	 */
	public function get_kolejka($id)
	{
		return DB::Select()->from('videos')
					->where('kolejka_id','=',$id)
					->execute();
	}
	
	/**
	 * zwraca nazwe ligi i kolejki
	 * @param  id video
	 * @return array - nazwa ligi i kolejki
	 */
	public function get_kolejka_liga_name($id)
	{
		$p = DB::Select(array('ligues.name','ligue_name'),array('kolejka.name','kolejka_name'))->from('ligues');
		$p->join('kolejka','left')->on('kolejka.ligue_id','=','ligues.id');
		$p->join('videos','left')->on('videos.kolejka_id','=','kolejka.id');
		$p->where('videos.id','=',$id);
		$names = $p->execute()->current();
		return array('liga'=>$names['ligue_name'],'kolejka'=>$names['kolejka_name']);
	}
	
	/**
	 * nastepna kolejka do paginacji widoku kolejki
	 * @param  id kolejka
	 * @return array ...
	 */
	public function pagination_kolejka($id,$direction)
	{
		// wybieranie id ligi na podstawie id kolejki
		$liga = DB::Select('ligue_id','created_at')->from('kolejka')
					->where('id','=',$id)
					->execute()->current();
		$liga_id = $liga['ligue_id'];
		// data dodania biezacej kolejki
		$current_data = $liga['created_at'];
		// wybieranie nastepnej kolejki (po dacie)
		$k = DB::Select()->from('kolejka');
		if($direction == 'next')
		{
			$k->where('created_at','>',$current_data);
			$k->and_where('ligue_id','=',$liga_id);
			$k->and_where('id','!=',$id);
			$k->order_by('created_at','asc');
		}else{
			$k->where('created_at','<',$current_data);
			$k->and_where('ligue_id','=',$liga_id);
			$k->and_where('id','!=',$id);
			$k->order_by('created_at','desc');
		}
		
		return $k->execute()->current();
	}
	
	public function get_video($id)
	{
		return DB::Select()->from('videos')
					->where('id','=',$id)
					->execute()->current();
	}
	
	public function get_comments_video($id)
	{
		return DB::Select()->from('comments')
			->where('page_id','=',$id)
			->order_by('created_at','desc')
			->execute();
	}
	
	public function save_comment_video($_POST,$id)
	{
		$content = strip_tags(Security::xss_clean($_POST['content']));
		$message = str_replace("\n", "<br/>", $content); 
		$imie = strip_tags(Security::xss_clean($_POST['imie']));
		$email = strip_tags(Security::xss_clean($_POST['email']));
		$strona = $_POST['strona'];
		if($strona == 'strona domowa [opcjonalnie]')
			$strona = '';
		DB::insert('comments', array('page_id','name','email','page','type','content','created_at'))->values(array($id,$imie,$email,$strona,'video',$message,time()))->execute();
		return mysql_insert_id();
	}
	
	public function delete_comment_video($id)
	{
		DB::delete('comments')->where('id','=',$id)->execute();
	}
	
	/**
	 * zapisuje wizyty w liczniku
	 * jezeli w tabeli visits_ip nie ma jeszcze IP klienta
	 *
	 * @param  video_id
	 * @return
	 */
	public function save_visit($id)
	{
		// sprawdzenie czy video o takim id istnieje
		$video = (bool) DB::select(array('COUNT("*")', 'total_count'))
				->from('videos')
				->where('id','=',$id)
				->execute()->get('total_count');
				
		if(!$video) return "no_video";
		
		$ip = $_SERVER['REMOTE_ADDR'];
		$exists = (bool) DB::select(array('COUNT("*")', 'total_count'))
				->from('visits_ip')
				->where('ip','=',$ip)
				->and_where('video_id','=',$id)
				->execute()->get('total_count');
		if(!$exists)
		{
			DB::update('visits')
				->set(array('number' => DB::expr('number + 1')))
			  	->where('video_id', '=', $id)
			  	->execute();
			// wpis do tabeli visits_ip
			DB::insert('visits_ip', array('video_id','ip'))->values(array($id,$ip))->execute();
		}
	}
	
	/**
	 * lista wszystkich analiz
	 */
	public function analizy_lista($limit,$offset)
	{
		return DB::Select()->from('pages')
			->where('category_id','=',4)
			->and_where('published','=',1)
			->order_by('created_at','desc')
			->limit($limit)
			->offset($offset)
			->execute();	
	}
	
/**
	 * liczba wszystkich (opublikowanych) analiz
	 */
	public function count_analizy()
	{
		return 	DB::select(array('COUNT("*")', 'total_count'))
				->from('pages')
				->where('category_id','=',4)
				->and_where('published','=',1)
				->execute()->get('total_count');
	}
}
?>